USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Admin_UpdateComputerName]    Script Date: 05/30/2012 15:19:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Admin_UpdateComputerName]
	
	@OldName varchar(255),
	@NewName varchar(255)
AS
BEGIN

	update Sessions set ComputerName = @NewName where ComputerName = @OldName
	
	update PowerEventLogs set ComputerName = @NewName where ComputerName = @OldName
	
	update ComputerConsumptions set ClientName = @NewName where ClientName = @OldName
	
	update Profiles set FQDN = @NewName where FQDN = @OldName
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[AutoLogouts_Add]    Script Date: 05/30/2012 15:20:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[AutoLogouts_Add]
	-- Add the parameters for the stored procedure here
	@ComputerName varchar(30),
	@UserName varchar(20),
	@LogoutTime datetime
AS
BEGIN
	SET NOCOUNT ON

	INSERT INTO dbo.AutoLogouts (ComputerName,UserName,LogoutTime) values (@ComputerName,@UserName,@LogoutTime);
	
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[AutoLogouts_Get]    Script Date: 05/30/2012 15:20:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[AutoLogouts_Get]
	@StartPeriod datetime,
	@EndPeriod datetime,
	@ComputerName varchar(255),
	@UserName varchar(255)	
AS
BEGIN
	SELECT ComputerName,UserName,LogoutTime from AutoLogouts WHERE LogoutTime >= @StartPeriod and LogoutTime <= @EndPeriod and ComputerName like @ComputerName and UserName like @UserName order by LogoutTime desc;
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[BottomLevelOUs_Get]    Script Date: 05/30/2012 15:20:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[BottomLevelOUs_Get]
	
	@Lang varchar(10) 
AS
BEGIN
	SELECT OU,Label,Lang from BottomLevelOUs where Lang = @Lang;

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[CentralControl_Get]    Script Date: 05/30/2012 15:20:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CentralControl_Get]
	
	@ControlKey varchar(255)
AS
BEGIN
	
	set nocount on
	
	select ControlValue from CentralControl where ControlKey = @ControlKey
	
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[CentralControl_ToggleItem]    Script Date: 05/30/2012 15:23:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CentralControl_ToggleItem]
	
	@ControlKey varchar(255),
	@Toggle int
AS
BEGIN
	
	set nocount on
	
	declare @tmpControlValue varchar(255)
	
	set @tmpControlValue = (select ControlValue from CentralControl where ControlKey = @ControlKey)

	if @Toggle = 1
	begin
		if @tmpControlValue = '0'
			set @tmpControlValue = '1'
		else if @tmpControlValue = '1'
			set @tmpControlValue = '0'
	end
			
	update CentralControl set LastModified = GETDATE(), ControlValue = @tmpControlValue where ControlKey = @ControlKey;
	
	select @tmpControlValue as 'ControlValue'
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[ClientRegistrations_Delete]    Script Date: 05/30/2012 15:23:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ClientRegistrations_Delete]
	-- Add the parameters for the stored procedure here
	@FQDN varchar(255),
	@MACAddress varchar(50),
	@IPAddress varchar(50),
	@OwnerID varchar(255),
	@Notes varchar(255)
AS
BEGIN
	
	delete from ClientRegistrations where @FQDN = FQDN or @MACAddress = MACAddress
	
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[ClientRegistrations_Get]    Script Date: 05/30/2012 15:23:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ClientRegistrations_Get]

AS
BEGIN
	
	select FQDN,IPAddress,MACAddress,LastRegistered,OwnerID,Notes from ClientRegistrations order by FQDN
END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[ClientRegistrations_GetByFQDN]    Script Date: 05/30/2012 15:28:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ClientRegistrations_GetByFQDN]
	-- Add the parameters for the stored procedure here
	@FQDN varchar(255)
AS
BEGIN
	
	select FQDN,IPAddress,MACAddress,Notes from ClientRegistrations where FQDN = @FQDN order by FQDN
END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[ClientRegistrations_GetByFQDNFilter]    Script Date: 05/30/2012 15:28:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ClientRegistrations_GetByFQDNFilter]
	@FQDN varchar(155)
AS
BEGIN
	
	select FQDN,IPAddress,MACAddress,LastRegistered,OwnerID,Notes from ClientRegistrations where FQDN like '%'+@FQDN+'%' order by FQDN
END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[ClientRegistrations_GetByOwnerID]    Script Date: 05/30/2012 15:28:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ClientRegistrations_GetByOwnerID]
	-- Add the parameters for the stored procedure here
	@OwnerID varchar(255)
AS
BEGIN
	
	select FQDN,IPAddress,MACAddress,Notes from ClientRegistrations where OwnerID = @OwnerID order by FQDN
END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[ClientRegistrations_GetLast100]    Script Date: 05/30/2012 15:28:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ClientRegistrations_GetLast100]

AS
BEGIN
	
	select top(100) FQDN,IPAddress,MACAddress,LastRegistered,OwnerID,Notes from ClientRegistrations order by LastRegistered desc
END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[ClientRegistrations_Set]    Script Date: 05/30/2012 15:29:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ClientRegistrations_Set]
	-- Add the parameters for the stored procedure here
	@FQDN varchar(255),
	@MACAddress varchar(50),
	@IPAddress varchar(50),
	@OwnerID varchar(255),
	@Notes varchar(255)
AS
BEGIN
	
	DECLARE @record_count INT
	declare @RecordExists int
	
	set @RecordExists = (select COUNT(*) from ClientRegistrations where @MACAddress = MACAddress)
	
	if @RecordExists = 1 
		update ClientRegistrations set FQDN = @FQDN, IPAddress = @IPAddress, LastRegistered = GETDATE(), OwnerID = @OwnerID, Notes = @Notes where MACAddress = @MACAddress
	else 
	begin		
		delete from ClientRegistrations where @FQDN = FQDN or @MACAddress = MACAddress
		insert into ClientRegistrations(FQDN,MACAddress,IPAddress,LastRegistered,OwnerID,Notes) values (@FQDN,@MACAddress,@IPAddress,GETDATE(),@OwnerID,@Notes)
	end
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[ClientRegistrations_SetOwnerID]    Script Date: 05/30/2012 15:29:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ClientRegistrations_SetOwnerID]
	-- Add the parameters for the stored procedure here
	@FQDN varchar(255),
	@OwnerID varchar(255)
AS
BEGIN
	
	update ClientRegistrations set OwnerID = @OwnerID where FQDN = @FQDN

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[ClientRegistrations_SetWithVersion]    Script Date: 05/30/2012 15:29:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ClientRegistrations_SetWithVersion]
	-- Add the parameters for the stored procedure here
	@FQDN varchar(255),
	@MACAddress varchar(50),
	@IPAddress varchar(50),
	@Version varchar(50)
	
AS
BEGIN
	
	DECLARE @record_count INT
	declare @RecordExists int
	
	set @RecordExists = (select COUNT(*) from ClientRegistrations where @MACAddress = MACAddress)
	
	if @RecordExists = 1 
		update ClientRegistrations set FQDN = @FQDN, IPAddress = @IPAddress, LastRegistered = GETDATE(), Version = @Version where MACAddress = @MACAddress
	else 
	begin		
		delete from ClientRegistrations where @FQDN = FQDN or @MACAddress = MACAddress
		insert into ClientRegistrations(FQDN,MACAddress,IPAddress,LastRegistered,Version) values (@FQDN,@MACAddress,@IPAddress,GETDATE(),@Version)
	end
	
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[CO2Emissions_Get]    Script Date: 05/30/2012 15:29:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CO2Emissions_Get]
AS
BEGIN

	SELECT CO2PerUnit,FromDate,ToDate from CO2Emissions order by FromDate; 

END



GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[CO2Emissions_GetForDate]    Script Date: 05/30/2012 15:29:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CO2Emissions_GetForDate]
	@RefDate datetime
AS
BEGIN

	SELECT CO2PerUnit,FromDate,ToDate from CO2Emissions where @RefDate >= FromDate and @RefDate <= ToDate; 

END



GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[CO2Emissions_Set]    Script Date: 05/30/2012 15:30:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CO2Emissions_Set]
	@CO2PerUnit smallint,
	@FromDate datetime,
	@ToDate datetime,
	@ClearTable smallint
AS
BEGIN

	if @ClearTable = 1
	begin
		delete from CO2Emissions
	end
	
	insert into CO2Emissions(CO2PerUnit,FromDate,ToDate) values (@CO2PerUnit,@FromDate,@ToDate)	

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[CombinedEvents_Get]    Script Date: 05/30/2012 15:30:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CombinedEvents_Get]
	
	@NumEvents int
AS
BEGIN
	SET NOCOUNT ON
	
	create table #temptable (ComputerName varchar(255),EventTime datetime,EventType varchar(50),UserName varchar(20),Version varchar(50))

	insert into #temptable (ComputerName,EventTime,EventType,UserName,Version) 
		SELECT top(@NumEvents) ComputerName,LoginTime,'LOGIN',UserName,PAWSClientVersion from Sessions where LoginTime IS NOT NULL order by LoginTime desc

	insert into #temptable (ComputerName,EventTime,EventType,UserName,Version) 
		SELECT top(@NumEvents) ComputerName,LogoutTime,'LOGOUT',UserName,PAWSClientVersion from Sessions where LogoutTime IS NOT NULL order by LogoutTime desc

	insert into #temptable (ComputerName,EventTime,EventType) 
		SELECT top(@NumEvents) ComputerName,EventTime,EventType from PowerEventLogs order by EventTime desc
		
	select top(@NumEvents) * from  #temptable order by EventTime desc

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[ComputerConsumptions_Get]    Script Date: 05/30/2012 15:30:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ComputerConsumptions_Get]

AS
BEGIN

	SELECT ClientName,BaseConsumption,MonitorConsumption,LastModified from ComputerConsumptions order by ClientName; 

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[ComputerConsumptions_GetClient]    Script Date: 05/30/2012 15:30:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ComputerConsumptions_GetClient]
	
	@ClientName varchar(255),
	@RefDate datetime
AS
BEGIN

	DECLARE @recordCount int
	
	SET @recordCount = (SELECT count(*) from ComputerConsumptions where ClientName = @ClientName)
	
	if @recordCount = 0
		SET @ClientName = 'DEFAULT_CONSUMPTION'
	
	SELECT @ClientName as ClientName,BaseConsumption,MonitorConsumption from ComputerConsumptions where ClientName = @ClientName 

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[ComputerConsumptions_Set]    Script Date: 05/30/2012 15:30:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ComputerConsumptions_Set]
	
	@ClientName varchar(255),
	@BaseConsumption smallint,
	@MonitorConsumption smallint,
	@ClearTable smallint
AS
BEGIN

	DECLARE @recordCount int
	
	if @ClearTable = 1
		delete from ComputerConsumptions
		
	SET @recordCount = (SELECT count(*) from ComputerConsumptions where ClientName = @ClientName)
	
	if @recordCount = 0	
		insert into ComputerConsumptions (ClientName,BaseConsumption,MonitorConsumption,LastModified) values (@ClientName,@BaseConsumption,@MonitorConsumption,GETDATE())
	else
		update ComputerConsumptions set BaseConsumption=@BaseConsumption, MonitorConsumption=@MonitorConsumption where ClientName = @ClientName 
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[ComputerConsumptions_SetClient]    Script Date: 05/30/2012 15:30:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ComputerConsumptions_SetClient]
	
	@ClientName varchar(255),
	@BaseConsumption smallint,
	@MonitorConsumption smallint
AS
BEGIN

	DECLARE @recordCount int
	
	insert into ComputerConsumptions (ClientName,BaseConsumption,MonitorConsumption,LastModified) values (@ClientName,@BaseConsumption,@MonitorConsumption,GETDATE())

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Domains_GetAll]    Script Date: 05/30/2012 15:30:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Domains_GetAll]
	
AS
BEGIN
	set nocount on

	SELECT FQDN,LDAP,StatsRootOU from Domains;

END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Domains_GetFQDNandLDAP]    Script Date: 05/30/2012 15:31:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Domains_GetFQDNandLDAP]
	
AS
BEGIN
	set nocount on

	SELECT FQDN,LDAP from Domains;

END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Domains_GetFQDNs]    Script Date: 05/30/2012 15:31:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Domains_GetFQDNs]
	
AS
BEGIN
	set nocount on

	SELECT FQDN from Domains;

END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Domains_GetStatsRootOU]    Script Date: 05/30/2012 15:31:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Domains_GetStatsRootOU]
	@FQDN varchar(255)
AS
BEGIN
	SELECT * from Domains where FQDN = @FQDN;
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[ElectricityPrices_Get]    Script Date: 05/30/2012 15:31:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ElectricityPrices_Get]
AS
BEGIN

	SELECT UnitPrice,FromDate,ToDate from ElectricityPrices order by FromDate; 

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[ElectricityPrices_GetForDate]    Script Date: 05/30/2012 15:32:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ElectricityPrices_GetForDate]
	@RefDate datetime
AS
BEGIN

	SELECT UnitPrice,FromDate,ToDate from ElectricityPrices where @RefDate >= FromDate and @RefDate <= ToDate; 

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[ElectricityPrices_Set]    Script Date: 05/30/2012 16:26:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ElectricityPrices_Set]
	@UnitPrice float,
	@FromDate datetime,
	@ToDate datetime,
	@ClearTable smallint
AS
BEGIN

	if @ClearTable = 1
	begin
		delete from ElectricityPrices
	end
	
	insert into ElectricityPrices(UnitPrice,FromDate,ToDate) values (@UnitPrice,@FromDate,@ToDate)	

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[LiveStatsBuildings_Get]    Script Date: 05/30/2012 16:26:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[LiveStatsBuildings_Get]
AS
BEGIN
	SELECT * from LiveStatsBuildings order by OrderIndex;

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[LiveStatsRooms_Get]    Script Date: 05/30/2012 16:27:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[LiveStatsRooms_Get]
AS
BEGIN
	SELECT * from LiveStatsRooms order by OU;

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[LiveStatsRooms_GetByGroup]    Script Date: 05/30/2012 16:27:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[LiveStatsRooms_GetByGroup]
	@GroupingKey varchar(100)
AS
BEGIN
	SELECT * from LiveStatsRooms where @GroupingKey = GroupingKey;

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[OrganisationSavingStatistics_GetAll]    Script Date: 05/30/2012 16:27:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[OrganisationSavingStatistics_GetAll]
	
AS
BEGIN
	SET NOCOUNT ON
	
	select * from OrganisationSavingStatistics
END



GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[OrganisationSavingStatistics_GetOUTotal]    Script Date: 05/30/2012 16:27:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[OrganisationSavingStatistics_GetOUTotal]

	@FromDate datetime,
	@ToDate datetime,
	@Scale int,
	@OU varchar(750)
AS
BEGIN
	SET NOCOUNT ON
		
	if @Scale = 0	
		select YEAR(Date) as Year, MONTH(Date) as Month, sum(SleepTime) as SleepTime, sum(CO2Savings) as CO2Savings, sum(CostSavings) as CostSavings from SavingStatistics where Date >= @FromDate and Date <= @ToDate and OU like '%'+@OU+'%' group by YEAR(Date), MONTH(Date) order by Year,Month
		
	if @Scale = 1
		select YEAR(Date) as Year, sum(SleepTime) as SleepTime, sum(CO2Savings) as CO2Savings, sum(CostSavings) as CostSavings from SavingStatistics where Date >= @FromDate and Date <= @ToDate and @OU = OU group by YEAR(Date) order by Year
END



GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[OrganisationSavingStatistics_GetTotals]    Script Date: 05/30/2012 16:27:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[OrganisationSavingStatistics_GetTotals]

	@FromDate datetime,
	@ToDate datetime,
	@Scale int	
AS
BEGIN
	SET NOCOUNT ON
	
	select * into #TmpRecords from OrganisationSavingStatistics
		
	if @Scale = 0	
		select YEAR(Date) as Year, MONTH(Date) as Month, sum(SleepTime) as SleepTime, sum(CO2Savings) as CO2Savings, sum(CostSavings) as CostSavings from #TmpRecords where Date >= @FromDate and Date <= @ToDate group by YEAR(Date), MONTH(Date) order by Year,Month
		
	if @Scale = 1
		select YEAR(Date) as Year, sum(SleepTime) as SleepTime, sum(CO2Savings) as CO2Savings, sum(CostSavings) as CostSavings from #TmpRecords where Date >= @FromDate and Date <= @ToDate group by YEAR(Date) order by Year
END



GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[OrganisationSavingStatistics_GetTotalsByDomain]    Script Date: 05/30/2012 16:27:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[OrganisationSavingStatistics_GetTotalsByDomain]

	@FromDate datetime,
	@ToDate datetime,
	@Scale int,
	@OU varchar(750)
AS
BEGIN
	SET NOCOUNT ON
	
	select * into #TmpRecords from OrganisationSavingStatistics where OU like '%'+@OU+'%'
		
	if @Scale = 0	
		select YEAR(Date) as Year, MONTH(Date) as Month, sum(SleepTime) as SleepTime, sum(CO2Savings) as CO2Savings, sum(CostSavings) as CostSavings from #TmpRecords where Date >= @FromDate and Date <= @ToDate group by YEAR(Date), MONTH(Date) order by Year,Month
		
	if @Scale = 1
		select YEAR(Date) as Year, sum(SleepTime) as SleepTime, sum(CO2Savings) as CO2Savings, sum(CostSavings) as CostSavings from #TmpRecords where Date >= @FromDate and Date <= @ToDate group by YEAR(Date) order by Year
END



GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[OrganisationSavingStatistics_Set]    Script Date: 05/30/2012 16:28:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[OrganisationSavingStatistics_Set]
	-- Add the parameters for the stored procedure here
	@OU varchar(750),
	@FromDate datetime,
	@ToDate datetime
AS
BEGIN
	SET NOCOUNT ON
	
	CREATE TABLE #TmpResults  	 (
	
		Date datetime,
		SleepTime bigint,
		CO2Savings decimal(18,2),
		CostSavings decimal(18,2)
	) 

	insert into #TmpResults EXEC SavingStatistics_GetTotalsForOrgSavings @OU, @FromDate, @ToDate

	declare @Counter int
	declare @totalRecords int
	declare @tmpDate datetime
	
	set @totalRecords = (select count(*) from #TmpResults)
	set @Counter = 0
	
	while @Counter < @totalRecords
	begin
	
		set @tmpDate = (select top(1) date from #TmpResults)
			
		delete from OrganisationSavingStatistics where 
			 OU = @OU and
			 Date = @tmpDate
		
		insert into OrganisationSavingStatistics select @OU,Date,SleepTime,CO2Savings,CostSavings,GETDATE() from #TmpResults
		
		set @Counter = @Counter + 1
		delete top(1) from #TmpResults
	end			
	
END



GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[PawsConfigurations_Get]    Script Date: 05/30/2012 16:28:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[PawsConfigurations_Get]
	
	@ConfigType smallint
	
AS
BEGIN

set nocount on

	if @ConfigType = 0
		SELECT ConfigKey,ConfigValue,ConfigDesc from PawsConfigurations where PAWSClient = 1 order by ConfigKey 
	if @ConfigType = 1
		SELECT ConfigKey,ConfigValue,ConfigDesc from PawsConfigurations where PAWSServer = 1 order by ConfigKey 
	if @ConfigType = 2
		SELECT ConfigKey,ConfigValue,ConfigDesc from PawsConfigurations where PAWSWeb = 1 order by ConfigKey 
	if @ConfigType = 3
		SELECT ConfigKey,ConfigValue,ConfigDesc from PawsConfigurations where PAWSClientManager = 1 order by ConfigKey 
	if @ConfigType = 4
		SELECT ConfigKey,ConfigValue,ConfigDesc from PawsConfigurations where PAWSManager = 1 order by ConfigKey 
		

END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[PawsConfigurations_Set]    Script Date: 05/30/2012 16:28:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[PawsConfigurations_Set]
	
	@Key varchar(255),
	@Value varchar(255),
	@Desc varchar(2000),
	@Type smallint,
	@ClearTable smallint
	
AS
BEGIN

set nocount on

-- clear out the relevant table
	if @ClearTable = 1
	begin
		if @Type = 0
			update PawsConfigurations set PAWSClient = 0 where ConfigKey = @Key
		else if @Type = 1
			update PawsConfigurations set PAWSServer = 0 where ConfigKey = @Key
		else if @Type = 2
			update PawsConfigurations set PAWSWeb = 0 where ConfigKey = @Key
		else if @Type = 3
			update PawsConfigurations set PAWSClientManager = 0 where ConfigKey = @Key
		else if @Type = 4
			update PawsConfigurations set PAWSManager = 0 where ConfigKey = @Key
	end
	
	
	if ((select count(*) from PawsConfigurations where ConfigKey = @Key) = 0)
		insert into PawsConfigurations(ConfigKey,ConfigValue,ConfigDesc) values (@Key,@Value,@Desc)
		
	
	if @Type = 0
		update PawsConfigurations set PAWSClient = 1, ConfigValue = @Value, ConfigDesc = @Desc where ConfigKey = @Key
	else if @Type = 1
		update PawsConfigurations set PAWSServer = 1, ConfigValue = @Value, ConfigDesc = @Desc where ConfigKey = @Key
	else if @Type = 2
		update PawsConfigurations set PAWSWeb = 1, ConfigValue = @Value, ConfigDesc = @Desc where ConfigKey = @Key
	else if @Type = 3
		update PawsConfigurations set PAWSClientManager = 1, ConfigValue = @Value, ConfigDesc = @Desc where ConfigKey = @Key
	else if @Type = 4
		update PawsConfigurations set PAWSManager = 1, ConfigValue = @Value, ConfigDesc = @Desc where ConfigKey = @Key
			
END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[PenDriveEvents_Set]    Script Date: 05/30/2012 16:28:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[PenDriveEvents_Set]
	-- Add the parameters for the stored procedure here
	@ComputerName varchar(255),
	@UserName varchar(255),
	@LogoutTime datetime
AS
BEGIN
	SET NOCOUNT ON

	INSERT INTO dbo.PenDriveEvents (ComputerName,UserName,LogoutTime) values (@ComputerName,@UserName,@LogoutTime);
	
END



GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[PowerEventLogs_Add]    Script Date: 05/30/2012 16:28:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[PowerEventLogs_Add]
	-- Add the parameters for the stored procedure here
	@ComputerName varchar(255),
	@EventType varchar(50),
	@EventTime datetime
AS
BEGIN
	
	DECLARE @record_count INT
	
	-- check that an event of this nature hasn't been logged in the last 10 seconds - in order to stop stuttering repetition
	SELECT @record_count = COUNT(*) from PowerEventLogs where ComputerName = @ComputerName and EventType = @EventType  and EventTime > (dateadd(second,-10,@EventTime));
	
	IF (@record_count = 0) 
		INSERT INTO PowerEventLogs (ComputerName,EventType,EventTime) values (@ComputerName,@EventType,@EventTime);
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[PowerEventLogs_CalcSleep]    Script Date: 05/30/2012 16:28:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[PowerEventLogs_CalcSleep]
	@ComputerName varchar(255),
	@OU varchar(750),
	@FromDate datetime,
	@ToDate datetime
AS
BEGIN

	CREATE TABLE #TmpTable (
		
		ComputerName varchar(255),
		EventType varchar(50),
		EventTime datetime
	)
	
	CREATE TABLE #Counts (
		
		Date datetime,
		SleepTime bigint,
		CO2Savings decimal(32,2),
		KwhSavings decimal(32,2),
		CostSavings decimal(32,2)
	)
	
	insert into #TmpTable select * from PowerEventLogs where ComputerName = @ComputerName and EventTime >= @FromDate and EventTime <= @ToDate order by EventTime
	
	insert into #TmpTable select top(1) * from PowerEventLogs where ComputerName = @ComputerName and EventTime < @FromDate order by EventTime desc
	
	-- if no event after current monitor period, must be today, so add a record to the current time
	if (select count(*) from PowerEventLogs where ComputerName = @ComputerName and EventTime > @ToDate) > 0
		insert into #TmpTable select top(1) * from PowerEventLogs where ComputerName = @ComputerName and EventTime > @ToDate order by EventTime
	else
		insert into #TmpTable(ComputerName,EventType,EventTime) values (@ComputerName,'SLEEP_SERVER',GETDATE())
		
	select * into #SortedTmpTable from #TmpTable order by EventTime 
	
	declare @TotalSleepRecords bigint
	declare @Counter bigint
	declare @TmpEventTime datetime
	declare @TmpEventType varchar(50)
	declare @Wattage smallint
	declare @CO2 smallint
	declare @ElecCost decimal(10,10)
			
	set @TotalSleepRecords = (select count(*) from #SortedTmpTable)
	set @Counter = 0
	declare @SleepPeriodStart datetime
	declare @SleepPeriodEnd datetime
	
	set @SleepPeriodStart = null
	set @SleepPeriodEnd = null
	
	declare @initFromDate datetime
	declare @initToDate datetime
	
	set @initFromDate = @FromDate
	set @initToDate = @ToDate
	
	-- initialise the count tmp database
	while @initFromDate < @initToDate
	begin	
		insert into #counts (Date,SleepTime,CO2Savings,KwhSavings,CostSavings) values (@initFromDate,0,0.0,0.0,0.0)
		set @initFromDate = dateadd(day,1,@initFromDate)
	end
	
	while (@Counter < @TotalSleepRecords)
	begin
	
		select top(1) @TmpEventTime = EventTime, @TmpEventType = EventType from #SortedTmpTable order by EventTime	
		delete top(1) from #SortedTmpTable	
		
		
		if @TmpEventType = 'SLEEP_SERVER' and @SleepPeriodStart is null
		begin
			set @SleepPeriodStart = @TmpEventTime
		end
		
		else if @TmpEventType <> 'SLEEP_SERVER' and @SleepPeriodStart is not null and @SleepPeriodEnd is null
			set @SleepPeriodEnd = @TmpEventTime 
	
		-- if last record, then set @SleepPeriodEnd to end of the day	
		else if @TmpEventType = 'SLEEP_SERVER' and @SleepPeriodStart is not null and @SleepPeriodEnd is null and @Counter = @TotalSleepRecords-1
		begin
			set @SleepPeriodEnd = @TmpEventTime 
		end
				
		if @SleepPeriodStart is not null and @SleepPeriodEnd is not null
		begin
			
			declare @CountDate datetime
			declare @theseMins bigint
				
			while datediff(second,@SleepPeriodStart,@SleepPeriodEnd) > 0 and @SleepPeriodStart < @ToDate
			begin
			
				set @theseMins = 0
						
				-- for 1st record to trim off time before start period
				if @SleepPeriodStart < @FromDate
					set @SleepPeriodStart = @FromDate
				
				set @CountDate = dateadd(second, 0, dateadd(day, datediff(day, 0, @SleepPeriodStart), 0))	
				
				-- if start date and end date are on same day
				if datediff(day,@SleepPeriodStart,@SleepPeriodEnd) = 0
				begin
					set @theseMins = round((datediff(minute,@SleepPeriodStart,@SleepPeriodEnd)),0)
					set @SleepPeriodStart = @SleepPeriodEnd
				end
				
				-- if start date and end date are on different days
				else if datediff(day,@SleepPeriodStart,@SleepPeriodEnd) > 0
				begin
					set @theseMins = round((datediff(minute,@SleepPeriodStart,dateadd(second, 0, dateadd(day, datediff(day, 0, @SleepPeriodStart)+1, 0)))),0)
					set @SleepPeriodStart = dateadd(second, 0, dateadd(day, datediff(day, 0, @SleepPeriodStart)+1, 0))
				end
				
				set @Wattage = dbo.ComputerConsumptions_Lookup(@ComputerName,@CountDate)
				
				set @CO2 = dbo.CO2Emissions_Lookup(@CountDate)
				set @ElecCost = dbo.ElectricityPrices_Lookup(@CountDate)
			
				if @theseMins > 0
				begin
					
					--declare @KwhSavings decimal(10,10)
					declare @KwhSavings decimal(32,2)
			
					set @KwhSavings = (((cast(@Wattage as decimal(10,0))*24.0)/1000.0)/1440.0)*cast(@theseMins as decimal(10,0))
						
						
					update #counts set SleepTime = SleepTime + @theseMins ,
										CO2Savings = CO2Savings + ((@KwhSavings*@CO2)/1000),
										KwhSavings = KwhSavings + @KwhSavings,
										CostSavings = CostSavings + (@KwhSavings*@ElecCost)
								where Date = @CountDate
								
					set @theseMins = 0
				end
			end
			
			set @SleepPeriodStart = null
			set @SleepPeriodEnd = null
		end
		
		set @Counter = @Counter + 1
	end
	
	select Date,SleepTime,KwhSavings,CO2Savings,CostSavings from #counts
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[PowerEventLogs_GetSavings]    Script Date: 05/30/2012 16:28:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[PowerEventLogs_GetSavings]
	@ComputerList varchar(MAX),
	@FromDate datetime,
	@ToDate datetime,
	@Scale smallint
AS
BEGIN

	set nocount on
	
	CREATE TABLE #counts (
		
		Date datetime,
		PAWSSleepTime bigint,
		WakeTime bigint,
		UserSleepTime bigint,
		TotalPAWSElecSavings decimal(38,10),
		TotalUserElecSavings decimal(38,10),
		TotalElecUsed decimal(38,10),
		TotalPAWSCO2savings decimal(38,10),
		TotalUserCO2savings decimal(38,10),
		TotalCO2Used decimal(38,10)
	)
	
	CREATE TABLE #tmpresults (
		
		EventType varchar(50),
		EventTime datetime,
		ComputerName varchar(255)
	)
	
	CREATE TABLE #comparison (
	
		CompareStartDate datetime,
		CompareEndDate datetime,
		CompareStartEventType varchar(50),
		CompareEndEventType varchar(50)
		
	)
	
	declare @totalrecords bigint
	declare @count bigint
	declare @EventType varchar(50)
	declare @EventTime datetime
	declare @FromEventType varchar(50)
	declare @FromEventTime datetime
	declare @ToEventType varchar(50)
	declare @ToEventTime datetime
	declare @EventLength bigint
	declare @CO2Figure bigint
	declare @ElecFigure decimal(10,10)
	declare @ComputerName varchar(255)
	
	select * into #computertable from paws_split_comma_separated_field(@ComputerList,'|')
	
	-- get the 1st record before the start period
	insert into #tmpresults (EventType,EventTime,ComputerName) select top(1) EventType,EventTime,ComputerName from PowerEventLogs  WHERE EventTime < @FromDate and ComputerName in (select computer from #computertable) order by EventTime desc
	
	-- get all the records between the timescale
	insert into #tmpresults (EventType,EventTime,ComputerName) select EventType,EventTime,ComputerName from PowerEventLogs  WHERE EventTime >= @FromDate and EventTime <= @ToDate and ComputerName in (select computer from #computertable) order by EventTime
	
	-- get the last record after the end period
	insert into #tmpresults (EventType,EventTime,ComputerName) select top(1) EventType,EventTime,ComputerName from PowerEventLogs  WHERE EventTime > @ToDate and ComputerName in (select computer from #computertable) order by EventTime
	
	set @totalrecords = (select count(*) from #tmpresults)
	set @count = 0
	
	set @ComputerName = (select top(1) computer from #computertable)

	declare @initStartTime datetime, @initEndTime datetime
	set @initStartTime = @FromDate
	set @initEndTime = @ToDate
		
	-- initialise the count tmp database
	while @initStartTime < @initEndTime
	begin
		insert into #counts (Date,PAWSSleepTime,WakeTime,UserSleepTime) values (@initStartTime,0,0,0)
		set @initStartTime = dateadd(day,1,@initStartTime)
	end
	
	--select * from #counts
	
	while (@count < @totalrecords)
	begin
		
		-- get and store the info for the FROM record
		set @FromEventType = (select top(1) EventType from #tmpresults)
		set @FromEventTime = (select top(1) EventTime from #tmpresults)
		
		-- pop the FROM record off the stack
		delete top(1) from #tmpresults
				
		-- get and store the info for the FROM record
		set @ToEventType = (select top(1) EventType from #tmpresults)
		set @ToEventTime = (select top(1) EventTime from #tmpresults)
		
		if @ToEventTime is null
			set @ToEventTime = GETDATE()
			
		insert into #comparison (CompareStartDate,CompareStartEventType,CompareEndDate,CompareEndEventType) values (@FromEventTime,@FromEventType,@ToEventTime,@ToEventType) 
		
		while @FromEventTime < @ToEventTime
		begin
		
			declare @startOfThisDay datetime
			set @startOfThisDay = @FromEventTime
			
			-- if the same day just get the event period, as is
			if datepart(year, @ToEventTime) = datepart(year, @FromEventTime) and
				datepart(month, @ToEventTime) = datepart(month, @FromEventTime) and
				datepart(day, @ToEventTime) = datepart(day, @FromEventTime)
			begin
				set @EventLength = datediff(millisecond,@FromEventTime,@ToEventTime)
				set @FromEventTime = dateadd(day,1,@FromEventTime)
			end
			
			-- otherwise use 23:59:59 and increment a day
			else
			begin
			
				declare @endOfDay datetime
				set @endOfDay = @FromEventTime
				
				set @endOfDay = DATEADD(ms, -2, DATEADD(dd, 1, DATEDIFF(dd, 0, @endOfDay)))
				
				set @EventLength = datediff(millisecond,@FromEventTime,@endOfDay)
				
				set @FromEventTime = DATEADD(ms, -1, DATEADD(dd, 1, DATEDIFF(dd, 0, @FromEventTime)))
			
			end
			
			set @startOfThisDay = DATEADD(ms, -1, DATEADD(dd, 0, DATEDIFF(dd, 0, @startOfThisDay)))
			
			if @FromEventType = 'SLEEP_SERVER' and (@ToEventType = 'WAKEUP' or @ToEventType is null) and @FromEventTime > @FromDate 	
				update #counts set PAWSSleepTime = PAWSSleepTime+@EventLength where DATE = @startOfThisDay
			
			else if (@FromEventType = 'CLIENT_SERVICE_STOP' or @FromEventType = 'CLIENT_SHUTDOWN') and (@ToEventType = 'CLIENT_SERVICE_START' or @ToEventType is null) and @FromEventTime > @FromDate
				update #counts set UserSleepTime = UserSleepTime+@EventLength where DATE = @startOfThisDay
			
			else if @FromEventTime > @FromDate 
				update #counts set WakeTime = WakeTime+@EventLength where DATE = @startOfThisDay
				
		end
		
		delete from #comparison
		
		set @count = @count + 1
	end
	
	DECLARE counts_cursor CURSOR FOR
		SELECT * FROM #counts
	
	OPEN counts_cursor
		
	declare @tmp_DATE datetime, @tmp_PAWSSLEEP bigint, @tmp_WAKE bigint, @tmp_USERSLEEP bigint, @tmp_TOTALELECPAWSSAVINGS decimal(38,10),@tmp_TOTALELECUSERSAVINGS decimal(38,10), @tmp_TOTALELECUSED decimal(38,10), @tmp_TOTALCO2PAWSSAVINGS decimal(38,10),@tmp_TOTALCO2USERSAVINGS decimal(38,10), @tmp_TOTALCO2USED decimal(38,10)
		
	fetch next FROM counts_cursor into @tmp_DATE,@tmp_PAWSSLEEP,@tmp_WAKE,@tmp_USERSLEEP,@tmp_TOTALELECPAWSSAVINGS,@tmp_TOTALELECUSERSAVINGS,@tmp_TOTALELECUSED,@tmp_TOTALCO2PAWSSAVINGS,@tmp_TOTALCO2USERSAVINGS,@tmp_TOTALCO2USED
		
	while @@FETCH_STATUS = 0
	begin
	
		declare @elec table (
			UnitPrice decimal(10,10),
			FromDate datetime,
			ToDate datetime
		)
		delete from @elec
		insert into @elec exec ElectricityPrices_GetForDate @tmp_DATE
	
		declare @co2 table (
			CO2PerUnit smallint,
			FromDate datetime,
			ToDate datetime
		)
		delete from @co2
		insert into @co2 exec CO2Emissions_GetForDate @tmp_DATE
		
		
		declare @consumption table (
			ClientName varchar(255),
			BaseConsumption smallint,
			MonitorConsumption smallint
		)
		delete from @consumption
		insert into @consumption exec ComputerConsumptions_GetClient @ComputerName,@tmp_DATE
	
		declare @TOTALELECPAWSSAVINGS decimal(38,10), @TOTALELECUSERSAVINGS decimal(38,10), @TOTALELECUSED decimal(38,10), @TOTALCO2PAWSSAVINGS decimal(38,10), @TOTALCO2USERSAVINGS decimal(38,10), @TOTALCO2USED decimal(38,10)

		-- Work out total electricity PAWS cost savings = Watts * 24 * 1000 (which is KW/h unit) / 86400000 (ms in a day) * number of PAWS sleep milliseconds * unit price of electricity 
		set @TOTALELECPAWSSAVINGS = ((((((select top(1) BaseConsumption from @consumption) + (select top(1) MonitorConsumption from @consumption))*24)/1000.0)/86400000)*@tmp_PAWSSLEEP)*(select top(1) UnitPrice from @elec)
		
		-- Work out total electricity saved by manual user shutdown
		set @TOTALELECUSERSAVINGS = ((((((select top(1) BaseConsumption from @consumption) + (select top(1) MonitorConsumption from @consumption))*24)/1000.0)/86400000)*@tmp_USERSLEEP)*(select top(1) UnitPrice from @elec)
		
		-- Work out total electricity used
		set @TOTALELECUSED = ((((((select top(1) BaseConsumption from @consumption) + (select top(1) MonitorConsumption from @consumption))*24)/1000.0)/86400000)*@tmp_WAKE)*(select top(1) UnitPrice from @elec)
		
		-- Work out total PAWS co2 savings = Watts * 24 * 1000 (which is KW/h unit) / 86400000 (ms in a day) * number of PAWS sleep milliseconds * co2 emissions per unit of electricity / 1000 (in kg)
		set @TOTALCO2PAWSSAVINGS = (((((((select top(1) BaseConsumption from @consumption) + (select top(1) MonitorConsumption from @consumption))*24)/1000.0)/86400000)*@tmp_PAWSSLEEP)*(select top(1) CO2PerUnit from @co2))/1000
		
		-- Work out total co2 saved by manual user shutdown
		set @TOTALCO2USERSAVINGS = (((((((select top(1) BaseConsumption from @consumption) + (select top(1) MonitorConsumption from @consumption))*24)/1000.0)/86400000)*@tmp_USERSLEEP)*(select top(1) CO2PerUnit from @co2))/1000
		
		-- Work out total co2 used
		set @TOTALCO2USED = (((((((select top(1) BaseConsumption from @consumption) + (select top(1) MonitorConsumption from @consumption))*24)/1000.0)/86400000)*@tmp_WAKE)*(select top(1) CO2PerUnit from @co2))/1000
		
		update #counts set TotalPAWSElecSavings = @TOTALELECPAWSSAVINGS, TotalUserElecSavings = @TOTALELECUSERSAVINGS, TotalElecUsed = @TOTALELECUSED, TotalPAWSCO2Savings = @TOTALCO2PAWSSAVINGS, TotalUserCO2Savings = @TOTALCO2USERSAVINGS, TotalCO2Used = @TOTALCO2USED where Date = @tmp_DATE
		
		fetch next FROM counts_cursor into @tmp_DATE,@tmp_PAWSSLEEP,@tmp_WAKE,@tmp_USERSLEEP,@tmp_TOTALELECPAWSSAVINGS,@tmp_TOTALELECUSERSAVINGS,@tmp_TOTALELECUSED,@tmp_TOTALCO2PAWSSAVINGS,@tmp_TOTALCO2USERSAVINGS,@tmp_TOTALCO2USED
		
	end
	
	close counts_cursor
	deallocate counts_cursor
	
	declare @startDate datetime, @endDate datetime, @endOfMonth datetime
	
	if @Scale = 2
	begin
	
		select * into #tmpcounts from #counts
		delete from #counts
		
		set @startDate = @FromDate
		set @endDate = @ToDate
			
		while @startDate < @endDate
		begin
		
			insert into #counts select @startDate,sum(PAWSSleepTime),sum(WakeTime),sum(UserSleepTime),sum(TotalPAWSElecSavings),sum(TotalUserElecSavings),sum(TotalElecUsed),sum(TotalPAWSCO2Savings),sum(TotalUserCO2Savings),sum(TotalCO2Used) from #tmpcounts where DATE >= @startDate and DATE < dateadd(month,1,@startDate)
			
			set @startDate = dateadd(month,1,@startDate)
		end
	end
	
	else if @Scale = 3
	begin
	
		select * into #tmpcounts2 from #counts
		delete from #counts
		
		
		set @startDate = @fromDate
		set @endDate = @toDate
			
		while @startDate < @endDate
		begin
		
			insert into #counts select @startDate,sum(PAWSSleepTime),sum(WakeTime),sum(UserSleepTime),sum(TotalPAWSElecSavings),sum(TotalUserElecSavings),sum(TotalElecUsed),sum(TotalPAWSCO2Savings),sum(TotalUserCO2Savings),sum(TotalCO2Used) from #tmpcounts2 where DATE >= @startDate and DATE < dateadd(year,1,@startDate)
			
			set @startDate = dateadd(year,1,@startDate)
		end	
	end

	select * from #counts
END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[PowerEventLogs_GetTotalEventCounts]    Script Date: 05/30/2012 16:29:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[PowerEventLogs_GetTotalEventCounts]
	@ToDate datetime,
	@Scale varchar(30),
	@Period smallint,
	@ComputerList varchar(MAX),
	@Delimiter varchar(2)
AS
BEGIN
	
	set nocount on
	
	CREATE TABLE #counts (
		
		YearVal smallint,
		MonthVal smallint,
		DayVal smallint,
		HourVal smallint,
		MinVal smallint,
		Count bigint,
		EventType varchar(50)
	)
	
	declare @COUNTER smallint
	declare @QUERY_START_DATE datetime
	
	set @COUNTER = 0
	set @QUERY_START_DATE = @ToDate
	
	while (@COUNTER < @Period)
	
	begin
	
		declare @TOTAL_SESSIONS bigint
		
		if @Scale = 'HOUR'
		begin
		
			set @QUERY_START_DATE = DATEADD(hour,-1,@QUERY_START_DATE)
			
			set @TOTAL_SESSIONS = (select count(*) from PowerEventLogs WHERE EventTime > @QUERY_START_DATE and EventTime < @ToDate and EventType = 'SLEEP_SERVER')
			
			insert into #counts (YearVal,MonthVal,DayVal,HourVal,MinVal,Count,EventType) values(
					DATEPART(year,@ToDate),
					DATEPART(month,@ToDate),
					DATEPART(day,@ToDate),
					DATEPART(hour,@ToDate),
					null,
					@TOTAL_SESSIONS,
					'SLEEP_SERVER'
			)
			
			set @TOTAL_SESSIONS = (select count(*) from PowerEventLogs WHERE EventTime > @QUERY_START_DATE and EventTime < @ToDate and EventType = 'WAKEUP')
			
			insert into #counts (YearVal,MonthVal,DayVal,HourVal,MinVal,Count,EventType) values(
					DATEPART(year,@ToDate),
					DATEPART(month,@ToDate),
					DATEPART(day,@ToDate),
					DATEPART(hour,@ToDate),
					null,
					@TOTAL_SESSIONS,
					'WAKEUP'
			)
			
			
			set @ToDate = DATEADD(hour,-1,@ToDate)
			
		end

		else if @SCALE = 'MIN'
		begin
		
			set @QUERY_START_DATE = DATEADD(minute,-1,@QUERY_START_DATE)
			
			set @TOTAL_SESSIONS = (select count(*) from PowerEventLogs WHERE EventTime > @QUERY_START_DATE and EventTime < @ToDate and EventType = 'SLEEP_SERVER')
			
			insert into #counts (YearVal,MonthVal,DayVal,HourVal,MinVal,Count,EventType) values(
					DATEPART(year,@ToDate),
					DATEPART(month,@ToDate),
					DATEPART(day,@ToDate),
					DATEPART(hour,@ToDate),
					DATEPART(minute,@ToDate),
					@TOTAL_SESSIONS,
					'SLEEP_SERVER'
			)

			set @TOTAL_SESSIONS = (select count(*) from PowerEventLogs WHERE EventTime > @QUERY_START_DATE and EventTime < @ToDate and EventType = 'WAKEUP')
			
			insert into #counts (YearVal,MonthVal,DayVal,HourVal,MinVal,Count,EventType) values(
					DATEPART(year,@ToDate),
					DATEPART(month,@ToDate),
					DATEPART(day,@ToDate),
					DATEPART(hour,@ToDate),
					DATEPART(minute,@ToDate),
					@TOTAL_SESSIONS,
					'WAKEUP'
			)

			set @ToDate = DATEADD(minute,-1,@ToDate)
			
		end

		
		set @COUNTER = @COUNTER + 1
	end
	
	select * from #counts
END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[PowerEventLogs_GetTotalStats]    Script Date: 05/30/2012 16:29:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[PowerEventLogs_GetTotalStats]

AS
BEGIN
	SET NOCOUNT ON

	declare @TotalSleeps bigint 
	declare @TotalPawsifications bigint 
	
	set @TotalSleeps = (select count(*) as Total from PowerEventLogs where EventType = 'SLEEP_SERVER')

	set @TotalPawsifications = (select count(distinct(ComputerName)) as Total from PowerEventLogs where EventType = 'SLEEP_SERVER')

	SELECT * into #tmptable from Domains;
	
	declare @TotalDomains smallint
	declare @DomainCounter smallint
	set @DomainCounter = 0
	
	set @TotalDomains = (select count(*) from #tmptable)
	
	declare @ThisDomain varchar(255)
	declare @DomainTotal bigint
	declare @DomainInfo varchar(MAX)
	declare @DomainSleeps bigint
	declare @DomainInUse bigint
	
	set @DomainInfo = ''
	
	while @DomainCounter < @TotalDomains
	begin
	
		set @ThisDomain = (select top(1) FQDN from #tmptable)
		delete top(1) from #tmptable
			
		-- get the total number of computers per domain	
		set @DomainTotal = (select count(distinct(ComputerName)) from Sessions where ComputerName like '%.'+@ThisDomain)
		
		-- get the total number of sleep events
		set @DomainSleeps = (select count(*) AS Total from PowerEventLogs a join
		(select ComputerName, max(EventTime) EventTime from PowerEventLogs group by ComputerName) b
			on a.ComputerName = b.ComputerName and a.EventTime = b.EventTime 
			where EventType = 'SLEEP_SERVER' and a.ComputerName like '%'+@ThisDomain)
		
		
		-- total in use for domain
		set @DomainInUse = (select count(distinct(ComputerName)) from Sessions where LoginTime is not null and LogoutTime is null and ComputerName like '%.'+@ThisDomain)
			
		set @DomainInfo = @DomainInfo + '|' + @ThisDomain+','+cast(@DomainSleeps as varchar)+','+cast(@DomainInUse as varchar)
				
		set @DomainCounter = @DomainCounter + 1
	end
	
	select @TotalSleeps as TotalSleeps, @TotalPawsifications as TotalPawsifications, @DomainInfo as DomainInfo	
END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[PowerEventLogs_Purge]    Script Date: 05/30/2012 16:29:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[PowerEventLogs_Purge]
	-- Add the parameters for the stored procedure here
	@RefDate datetime
AS
BEGIN
	
	declare @RecordsToBeMoved bigint 
	declare @RecordsBeforeCopy bigint 
	
	set @RecordsToBeMoved = (select count(*) from PowerEventLogs where EventTime < @RefDate)
	set @RecordsBeforeCopy = (select count(*) from PurgedPowerEventLogs)

	insert into PurgedPowerEventLogs select * from PowerEventLogs where EventTime < @RefDate
	
	declare @RecordsAfterCopy bigint 
	set @RecordsAfterCopy = (select count(*) from PurgedPowerEventLogs)
	
	if @RecordsAfterCopy = @RecordsBeforeCopy + @RecordsToBeMoved
		delete from PowerEventLogs where EventTime < @RefDate
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Profiles_Add]    Script Date: 05/30/2012 16:29:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Profiles_Add]
	-- Add the parameters for the stored procedure here
	
	@SleepPeriodStart varchar(5),
	@SleepPeriodEnd varchar(5),
	@IdlePeriod int,
	@Days varchar(50),
	@Filter varchar(50),
	@Inheritance smallint,
	@Enabled int,
	@CreatedBy varchar(255),
	@OU varchar(1000),
	@FQDN varchar(255),
	@SleepMode smallint
AS
BEGIN
	SET NOCOUNT ON

	INSERT INTO Profiles
           ([Filter]
           ,[OU]
           ,[Inheritance]
           ,[Created]
           ,[lastModified]
           ,[Enabled]
           ,[CreatedBy]
           ,[LastModifiedBy]
           ,[SleepPeriodStart]
           ,[SleepPeriodEnd]
           ,[IdlePeriod]
           ,[Days]
           ,[FQDN]
           ,[SleepMode])
     VALUES
           (@Filter, 
           @OU,
           @Inheritance,
           GETDATE(),
           GETDATE(),
           @Enabled,
           @CreatedBy,
           @CreatedBy,
           @SleepPeriodStart,
           @SleepPeriodEnd,
           @IdlePeriod,
           @Days,
           @FQDN,
           @SleepMode
           );


END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Profiles_Delete]    Script Date: 05/30/2012 16:29:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Profiles_Delete]
	-- Add the parameters for the stored procedure here
	@ID bigint
AS
BEGIN
	SET NOCOUNT ON

	DELETE FROM Profiles WHERE ID = @ID;
	
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Profiles_Edit]    Script Date: 05/30/2012 16:30:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Profiles_Edit]
	-- Add the parameters for the stored procedure here
	@ID bigint,
	@SleepPeriodStart varchar(5),
	@SleepPeriodEnd varchar(5),
	@IdlePeriod int,
	@Days varchar(50),
	@Filter varchar(50),
	@Inheritance smallint,
	@Enabled int,
	@SleepMode smallint
AS
BEGIN
	SET NOCOUNT ON

	UPDATE Profiles set SleepPeriodStart=@SleepPeriodStart, SleepPeriodEnd=@SleepPeriodEnd,IdlePeriod=@IdlePeriod,Days=@Days,Filter=@Filter,Inheritance=@Inheritance,Enabled=@Enabled,LastModified=GETDATE(),LastModifiedBy='admin',SleepMode=@SleepMode where ID = @ID;
	
END


	

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Profiles_Get]    Script Date: 05/30/2012 16:30:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [dbo].[Profiles_Get]
	
AS
BEGIN

	SELECT * from Profiles;
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Profiles_GetByOUandFQDN]    Script Date: 05/30/2012 16:30:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Profiles_GetByOUandFQDN]
	@OU varchar(1000),
	@ComputerList varchar(MAX),
	@Delimiter varchar(1)
AS
BEGIN

	select * into #computertable from paws_split_comma_separated_field(@ComputerList,@Delimiter)
	
	SELECT * from Profiles where FQDN in (select computer from #computertable) or (@OU like '%'+OU+'%' and Inheritance = 1) or @OU = OU;

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Profiles_GetCount]    Script Date: 05/30/2012 16:30:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Profiles_GetCount]
	@FQDN varchar(255)
AS
BEGIN
	SELECT count(*) as Total from Profiles where FQDN = @FQDN;

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Profiles_GetForAlias]    Script Date: 05/30/2012 16:30:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Profiles_GetForAlias]
	@FQDN varchar(255)
AS
BEGIN
	SELECT ID,Filter,OU,Inheritance,CreatedBy,LastModifiedBy,Created,LastModified,Days,SleepPeriodStart,SleepPeriodEnd,IdlePeriod,Enabled,SleepMode,ID from Profiles where FQDN like @FQDN;

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Profiles_GetForOU]    Script Date: 05/30/2012 16:30:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Profiles_GetForOU]
	@FQDN varchar(255)
AS
BEGIN
	SELECT Filter,OU,Inheritance,CreatedBy,LastModifiedBy,Created,LastModified,Days,SleepPeriodStart,SleepPeriodEnd,IdlePeriod,Enabled,SleepMode,ID from Profiles where charindex(OU,@FQDN) > 0 or  charindex(@FQDN,OU) > 0 and Inheritance = 1;

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[RemoteInstalls_Add]    Script Date: 05/30/2012 16:31:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[RemoteInstalls_Add]
	-- Add the parameters for the stored procedure here
	@FQDN varchar(255),
	@InstallType smallint,
	@Domain varchar(255),
	@IdleLoggerInstall smallint,
	@PAWSManagerInstall smallint,
	@JavaInstall smallint,
	@CleanDestFolder smallint,
	@PAWSLibsInstall smallint,
	@PAWSConfigInstall smallint
AS
BEGIN
	SET NOCOUNT ON

	INSERT INTO dbo.RemoteInstalls (FQDN,InstallType,Domain,Status,RequestTime,IdleLoggerInstall,PAWSManagerInstall,JavaInstall,CleanDestFolder,PAWSLibsInstall,PAWSConfigInstall) values (@FQDN,@InstallType,@Domain,0,GETDATE(),@IdleLoggerInstall,@PAWSManagerInstall,@JavaInstall,@CleanDestFolder,@PAWSLibsInstall,@PAWSConfigInstall);
	
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[RemoteInstalls_Get]    Script Date: 05/30/2012 16:31:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[RemoteInstalls_Get]
	@Domain varchar(255),
	@Status smallint
AS
BEGIN
	SET NOCOUNT ON

	select ID,FQDN,InstallType,IdleLoggerInstall,JavaInstall,CleanDestFolder,PAWSLibsInstall,PAWSConfigInstall,PAWSManagerInstall from dbo.RemoteInstalls where Status = @Status and Domain = @Domain 
	
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[RemoteInstalls_GetInfo]    Script Date: 05/30/2012 16:31:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[RemoteInstalls_GetInfo]
	@Records int
AS
BEGIN
	SET NOCOUNT ON

	select top(@Records) ID,FQDN,Domain,InstallType,RequestTime,LastUpdated,Status from dbo.RemoteInstalls order by LastUpdated desc
	
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[RemoteInstalls_Update]    Script Date: 05/30/2012 16:31:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[RemoteInstalls_Update]
	-- Add the parameters for the stored procedure here
	@ID bigint,
	@Status smallint
AS
BEGIN
	SET NOCOUNT ON

	UPDATE dbo.RemoteInstalls set Status=@Status, LastUpdated = GETDATE() where ID = @ID
	
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[SavingStatistics_Archive]    Script Date: 05/30/2012 16:31:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SavingStatistics_Archive]
	-- Add the parameters for the stored procedure here
	@ComputerList varchar(MAX),
	@Delimiter varchar(1),
	@OU varchar(750),
	@FromDate datetime,
	@ToDate datetime
AS
BEGIN
	SET NOCOUNT ON

	CREATE TABLE #Counts (
		
		Date datetime,
		SleepTime bigint,
		CO2Savings decimal(32,2),
		KwhSavings decimal(32,2),
		CostSavings decimal(32,2)
	)
	
	select * into #computertable from paws_split_comma_separated_field(@ComputerList,@Delimiter)
	
	declare @TotalComputers int
	declare @Counter int
	
	set @TotalComputers = (select count(*) from #computertable)
	set @Counter = 0
	
	insert into #Counts EXEC dbo.PowerEventLogs_CalcSleep 'dummy', @OU, @FromDate, @ToDate
	
	select * from #Counts
	
	while @Counter < @TotalComputers
	begin
		
		declare @ComputerName varchar(255)
		
		set @ComputerName = (select top(1) computer from #computertable)
		delete top(1) from #computertable
		
		insert into #Counts EXEC dbo.PowerEventLogs_CalcSleep @ComputerName, @OU, @FromDate, @ToDate
	
		set @Counter = @Counter + 1
	end
	
	delete from SavingStatistics where @OU = OU and Date >= @FromDate and Date < @ToDate
	
	
	insert into SavingStatistics 
		select @OU as OU,Date as Date,sum(SleepTime) as SleepTime,sum(CO2Savings) as CO2Savings,sum(KwhSavings) as KwhSavings,sum(CostSavings) as CostSavings,GETDATE() from #Counts group by Date
			
	select 0
END



GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[SavingStatistics_Get]    Script Date: 05/30/2012 16:31:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SavingStatistics_Get]
	-- Add the parameters for the stored procedure here
	@OU varchar(MAX),
	@Date datetime
AS
BEGIN
	SET NOCOUNT ON

	CREATE TABLE #TmpSavings	 (
		
		OU varchar(750),
		Date datetime,
		SleepTime bigint,
		Kwhsavings decimal(18,2),
		CO2Savings decimal(18,2),
		CostSavings decimal(18,2)
	)

	--if	@Day is not null and @Month is not null and @Year is not null
	--begin
		insert into #TmpSavings select * from SavingStatistics where OU like '%'+@OU+'%' and Date = @Date
	--end
	--if @Day is null and @Month is not null and @Year is not null
--	begin
--		insert into #TmpSavings select * from SavingStatistics where OU like '%'+@OU+'%' and YEAR = @YEAR and MONTH = @MONTH;
--	end
--	if @Day is null and @Month is null and @Year is not null
--	begin
--		insert into #TmpSavings select * from SavingStatistics where OU like '%'+@OU+'%' and YEAR = @YEAR;
--	end
	
	select * from #TmpSavings
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[SavingStatistics_GetTotalsForOrgSavings]    Script Date: 05/30/2012 16:32:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SavingStatistics_GetTotalsForOrgSavings]
	-- Add the parameters for the stored procedure here
	@OU varchar(MAX),
	@FromDate datetime,
	@ToDate datetime
AS
BEGIN
	SET NOCOUNT ON
	
	select Date, SUM(SleepTime) as TotalSleepTime, SUM(CO2Savings) as TotalCO2Savings, SUM(CostSavings) as TotalCostSavings from SavingStatistics  where OU like '%' + @OU + '%' and Date >= @FromDate and Date <= @ToDate group by Date order by Date
	
END



GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[SavingStatistics_GetTotalsForPeriod]    Script Date: 05/30/2012 16:32:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SavingStatistics_GetTotalsForPeriod]
	-- Add the parameters for the stored procedure here
	@OU varchar(MAX),
	@FromDate datetime,
	@ToDate datetime,
	@Scale smallint
AS
BEGIN
	SET NOCOUNT ON

	select * into #oulist from paws_split_comma_separated_field(@OU,'***')
	
	-- if day scale
	if @Scale = 0
	begin
		select OU, YEAR(Date) as Year, MONTH(Date) as Month, DAY(Date) as Day, SUM(SleepTime) as TotalSleepTime, SUM(CO2Savings) as TotalCO2Savings, SUM(CostSavings) as TotalCostSavings from SavingStatistics  where OU in (select computer from #oulist) and Date >= @FromDate and Date <= @ToDate  group by YEAR(Date),MONTH(Date),DAY(Date),OU order by Year,Month,Day,(select id from #oulist where computer = OU)
	end
	
	-- if a month scale
	if @Scale = 1
	begin
		--select OU, YEAR(Date) as Year, MONTH(Date) as Month, SUM(TotalSleepTime) as TotalSleepTime, SUM(TotalCO2Savings) as TotalCO2Savings, SUM(TotalCostSavings) as TotalCostSavings from #counts  where OU is not null group by YEAR(Date), MONTH(Date),OU order by Year,Month,(select id from #oulist where computer = OU)
		select OU, YEAR(Date) as Year, MONTH(Date) as Month, SUM(SleepTime) as TotalSleepTime, SUM(CO2Savings) as TotalCO2Savings, SUM(CostSavings) as TotalCostSavings from SavingStatistics where OU in (select computer from #oulist) and Date >= @FromDate and Date <= @ToDate group by YEAR(Date), MONTH(Date),OU order by Year,Month,(select id from #oulist where computer = OU)
	end
	
	-- if a year scale
	if @Scale = 2
	begin
		select OU, YEAR(Date) as Year, SUM(SleepTime) as TotalSleepTime, SUM(CO2Savings) as TotalCO2Savings, SUM(CostSavings) as TotalCostSavings from SavingStatistics  where OU in (select computer from #oulist)  and Date >= @FromDate and Date <= @ToDate  group by YEAR(Date),OU order by Year,(select id from #oulist where computer = OU)
	end
END



GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[SavingStatistics_Validate]    Script Date: 05/30/2012 16:32:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SavingStatistics_Validate]
	-- Add the parameters for the stored procedure here
	@OU varchar(750),
	@RefDate datetime
AS
BEGIN
	SET NOCOUNT ON

	Declare @Total bigint
	Declare @Validation smallint 
	set @Total = (select count(*) from SavingStatistics where OU = @OU and Date = @RefDate)
	
	if @Total > 0
		set @Validation = 0
	else
		set @Validation = 1
		
	select @Validation as Result
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Sessions_Add]    Script Date: 05/30/2012 16:32:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Sessions_Add]
	-- Add the parameters for the stored procedure here
	@ComputerName varchar(255),
	@UserName varchar(255),
	@Language varchar(255),
	@BuildVersion varchar(255),
	@Domain varchar(255),
	@SerialNumber varchar(20),
	@PAWSClientVersion varchar(50),
	@OperatingSystem varchar(50)
AS
BEGIN
	SET NOCOUNT ON

	declare @lastLogin datetime
	
	-- to stop duplicate login sessions, check that the last login hasn't been in the past 10 seconds
	set @lastLogin = (select top(1) LoginTime from Sessions where ComputerName = @ComputerName)
	
	if @lastLogin is not null
	begin
		if @lastLogin < dateadd(second,-10000,GETDATE())
		begin
			INSERT INTO Sessions(ComputerName,UserName,LoginTime,Language,BuildVersion,Domain,SerialNumber,PAWSClientVersion,OperatingSystem) values (@ComputerName,@UserName,GETDATE(),@Language,@BuildVersion,@Domain,@SerialNumber,@PAWSClientVersion,@OperatingSystem);
			SELECT ID as SESSION_ID FROM Sessions WHERE ID = @@IDENTITY;
		end
	end
	
	else
	begin
		INSERT INTO Sessions (ComputerName,UserName,LoginTime,Language,BuildVersion,Domain,SerialNumber,PAWSClientVersion,OperatingSystem) values (@ComputerName,@UserName,GETDATE(),@Language,@BuildVersion,@Domain,@SerialNumber,@PAWSClientVersion,@OperatingSystem);
		SELECT ID as SESSION_ID FROM Sessions WHERE ID = @@IDENTITY;
	
	end
END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Sessions_Close]    Script Date: 05/30/2012 16:32:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Sessions_Close]
	-- Add the parameters for the stored procedure here
	@ID bigint,
	@LogoutTime datetime
AS
BEGIN
	SET NOCOUNT ON
	UPDATE Sessions SET LogoutTime = @LogoutTime WHERE ID = @ID

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Sessions_FixUnClosed]    Script Date: 05/30/2012 16:32:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Sessions_FixUnClosed]
	-- Add the parameters for the stored procedure here
	@ComputerName varchar(255)
AS
BEGIN
	UPDATE Sessions SET LogoutTime = DATEADD(hh,1,LoginTime) where ComputerName = @ComputerName and LogoutTime IS NULL and GETDATE()-LoginTime > 360000
	
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Sessions_Get]    Script Date: 05/30/2012 16:33:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Sessions_Get]

	@FromDate datetime,
	@ToDate datetime,
	@ComputerName varchar(255),
	@UserName varchar(255),
	@LoginFilter smallint,
	@LogoutFilter smallint,
	@SleepFilter smallint,
	@WakeFilter smallint,
	@UpdateFilter smallint,
	@AutoLogoutFilter smallint
AS
BEGIN

	set nocount on

	create table #temptable (
		
		EventTime datetime,
		EventType varchar(255),
		ComputerName varchar(255),
		UserName varchar(255)
	)
	
	if @LoginFilter = 1
		INSERT INTO #temptable(EventTime,EventType,ComputerName,UserName) 
			SELECT LoginTime,'LOGIN',ComputerName,UserName from Sessions WHERE ComputerName like '%'+@ComputerName+'%' and UserName like '%'+@UserName+'%' and LoginTime is not null and LoginTime >= @FromDate and LoginTime <= @ToDate;
	
	if @LogoutFilter = 1
		INSERT INTO #temptable(EventTime,EventType,ComputerName,UserName) 
			SELECT LogoutTime,'LOGOUT',ComputerName,UserName from Sessions WHERE ComputerName like '%'+@ComputerName+'%' and UserName like '%'+@UserName+'%' and LogoutTime is not null and LogoutTime >= @FromDate and LogoutTime <= @ToDate;
	
	if @SleepFilter = 1
		INSERT INTO #temptable(EventTime,EventType,ComputerName) 
			SELECT EventTime,EventType,ComputerName from PowerEventLogs WHERE ComputerName like '%'+@ComputerName+'%' and EventType = 'SLEEP_SERVER' and EventTime >= @FromDate and EventTime <= @ToDate;
			
	if @WakeFilter = 1
		INSERT INTO #temptable(EventTime,EventType,ComputerName) 
			SELECT EventTime,EventType,ComputerName from PowerEventLogs WHERE ComputerName like '%'+@ComputerName+'%' and EventType = 'WAKEUP' and EventTime >= @FromDate and EventTime <= @ToDate;
	
	if @AutoLogoutFilter = 1
		INSERT INTO #temptable(EventTime,EventType,ComputerName,UserName) 
			SELECT LogoutTime,'AUTOLOGOUT',ComputerName,UserName from AutoLogouts WHERE ComputerName like '%'+@ComputerName+'%' and UserName like '%'+@UserName+'%' and LogoutTime >= @FromDate and LogoutTime <= @ToDate;
				
							
	select * from #temptable order by EventTime desc
	
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Sessions_GetForComputers]    Script Date: 05/30/2012 16:33:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Sessions_GetForComputers]
	
	@ComputerName varchar(max),
	@Delimiter varchar(10),
	@UserName varchar(20),
	@FromDate datetime,
	@ToDate datetime
	
AS
BEGIN
	SET NOCOUNT ON;

	if @UserName IS NOT NULL
		SELECT ComputerName,UserName,LoginTime,LogoutTime,Language from Sessions SL where exists (SELECT computer from paws_split_comma_separated_field(@ComputerName,@Delimiter) WHERE SL.ComputerName = computer and SL.UserName = @UserName and SL.LoginTime >= @FromDate and SL.LoginTime <= @ToDate and SL.LogoutTime IS NOT NULL) order by LoginTime desc
	else
		SELECT ComputerName,UserName,LoginTime,LogoutTime,Language from Sessions SL where exists (SELECT computer from paws_split_comma_separated_field(@ComputerName,@Delimiter) WHERE SL.ComputerName = computer and SL.LoginTime >= @FromDate and SL.LoginTime <= @ToDate  and SL.LogoutTime IS NOT NULL) order by LoginTime desc
		
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Sessions_GetLastLoginForComputer]    Script Date: 05/30/2012 16:33:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Sessions_GetLastLoginForComputer]	
	@ComputerName varchar(255)
AS
BEGIN
	SELECT TOP(1) LoginTime,LogoutTime from Sessions WHERE ComputerName = @ComputerName order by LoginTime desc;
END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Sessions_GetLiveStats]    Script Date: 05/30/2012 16:33:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Sessions_GetLiveStats]
	-- Add the parameters for the stored procedure here
	@ComputerList varchar(MAX),
	@Delimiter varchar(2)
AS
BEGIN

	set nocount on
	
	CREATE TABLE #statusinfo (
		
		ComputerName varchar(255),
		UserName varchar(255),
		LogStatus varchar(20),
		LogTime datetime,
		BuildVersion varchar(20),
		ClientVersion varchar(50),
		LastPowerEventTime datetime
	)
	
	
	select * into #computertable from paws_split_comma_separated_field(@ComputerList,@Delimiter) 
	
	declare @totalComputers int 
	set @totalComputers = (select count(*) from #computertable)
	
	declare @computerCount int
	set @computerCount = 1
		
	declare @recordCount int
	declare @lastLogin datetime
	declare @computerName varchar(255)
	declare @userLogin varchar(255)
	declare @osBuild varchar(20)
	declare @pawsVersion varchar(50)
	declare @pawsVersionInClientReg varchar(50)
	declare @lastWake datetime
	declare @lastSleep datetime
	declare @lastEvent varchar(255)
	declare @lastEventTime datetime
	
	while @computerCount <= @totalComputers
	begin
		
		set @computerName = (select computer from #computertable where @computerCount = id)
		
		-- check if the computer has ever logged in
		select top(1) * into #recordTable from Sessions WHERE ComputerName = @ComputerName order by LoginTime desc
		set @recordCount = (select count(*) FROM #recordTable)
		
		-- no login records for this computer...
		if @recordCount = 0 
			insert into #statusinfo(ComputerName,LogStatus,LogTime) values (@ComputerName,'NO_DATA',null)
			
		-- there are login records for this computer...
		else
			begin
			
			-- check for active login
			set @recordCount = (select count(*) FROM #recordTable WHERE LoginTime is not null and LogoutTime is null)
			SELECT top(1) @lastLogin = LoginTime, @userLogin = UserName, @osBuild = BuildVersion, @pawsVersion = PAWSClientVersion FROM #recordTable
			
			-- if the version number in ClientRegisration is not null, then overwrite with this value as more up to date
			set @pawsVersionInClientReg = (select Version from ClientRegistrations where @ComputerName = FQDN)
			
			if @pawsVersionInClientReg is not null
				set @pawsVersion = @pawsVersionInClientReg
			
			-- now query for the last power events...
			select top(1) @lastEvent = EventType, @lastEventTime = EventTime from PowerEventLogs where ComputerName = @ComputerName and EventType != 'CLIENT_SERVICE_START' order by EventTime desc
			
			-- no one logged in, log this and the time of last login
			if @recordCount = 0	
			begin
				set @lastLogin = (SELECT LogoutTime FROM #recordTable)
				
				insert into #statusinfo(ComputerName,LogStatus,LogTime,UserName,BuildVersion,ClientVersion) values (@computerName,'LOGGED_OUT',@lastLogin,@userLogin,@osBuild,@pawsVersion)
									
				-- if last event is a wakeup event and occurred after logout, then set as wake
				if @lastEvent = 'WAKEUP' and @lastEventTime > @lastLogin
					update #statusinfo set LogStatus = 'WAKEUP' where ComputerName = @computerName
				
			end
				
			-- a user is logged into this computer
			else
			begin
				insert into #statusinfo(ComputerName,LogStatus,LogTime,UserName,BuildVersion,ClientVersion) values (@computerName,'LOGGED_IN',@lastLogin,@userLogin,@osBuild,@pawsVersion)
			end
			
			if @lastEventTime > @lastLogin  and @LastEvent = 'SLEEP_SERVER'
				update #statusinfo set LogStatus = @lastEvent where ComputerName = @computerName
		
			
			update #statusinfo set LastPowerEventTime = @lastEventTime where ComputerName = @computerName
			
			end	
					
		drop table #recordtable
			
		set @computerCount = @computerCount + 1
	end
	
	select * from #statusinfo order by ComputerName
END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Sessions_GetNumberSessions]    Script Date: 05/30/2012 16:33:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Sessions_GetNumberSessions]
	@FromDate datetime,
	@ToDate datetime,
	@ComputerList varchar(MAX),
	@Delimiter varchar(2)
	
AS
BEGIN
	
	set nocount on
	
	CREATE TABLE #counts (
		
		DATE datetime,
		PERIOD1 bigint,
		PERIOD2 bigint,
		PERIOD3 bigint
	)
	
	CREATE TABLE #tmpSessions (
		
		ComputerName varchar(255),
		UserName varchar(255),
		LoginTime datetime,
		LogoutTime datetime,
		Language varchar(50),
		ID bigint,
		BuildVersion varchar(20),
		Domain varchar(255),
		SerialNumber varchar(20),
		PAWSClientVersion varchar(50),
		OperatingSystem varchar(50)
	)
	
	select * into #computertable from paws_split_comma_separated_field(@ComputerList,@Delimiter)

	declare @initStartTime datetime, @initEndTime datetime, @loginTime datetime
	set @initStartTime = @FromDate
	set @initEndTime = @ToDate
	declare @NumComputers bigint
	
	while @initStartTime < @initEndTime
	begin	
		insert into #counts (DATE,PERIOD1,PERIOD2,PERIOD3) values (@initStartTime,0,0,0)
		set @initStartTime = dateadd(day,1,@initStartTime)
	end
	
	declare @UTIL_0000_0800 bigint
	declare @UTIL_0800_1800 bigint
	declare @UTIL_1800_0000 bigint

	if @ComputerList = '*'
	begin
		insert into #tmpSessions select ComputerName,UserName,LoginTime,LogoutTime,Language,ID,BuildVersion,Domain,SerialNumber,PAWSClientVersion,OperatingSystem from Sessions WHERE LoginTime > @FromDate and LoginTime < @ToDate and LogoutTime is not null
		set @NumComputers = (select count(distinct(ComputerName)) from Sessions)
	end
		
	else if charindex('%',@ComputerList) = 1
	begin
		set @ComputerList = (select replace(@ComputerList,'*',''))
		insert into #tmpSessions select ComputerName,UserName,LoginTime,LogoutTime,Language,ID,BuildVersion,Domain,SerialNumber,PAWSClientVersion,OperatingSystem from Sessions WHERE LoginTime > @FromDate and LoginTime < @ToDate and LogoutTime is not null and ComputerName like @ComputerList
		set @NumComputers = (select count(distinct(ComputerName)) from Sessions where ComputerName like @ComputerList)
	end
	
	else
	begin
		insert into #tmpSessions select * from Sessions WHERE LoginTime > @FromDate and LoginTime < @ToDate and exists (SELECT computer from #computertable WHERE ComputerName = computer) and LogoutTime is not null	
		set @NumComputers = (select count(*) from #computertable)
	end
	
	declare @COUNTER bigint, @TOTALRECORDS bigint, @SESSION_LENGTH bigint, @FromTime datetime, @ToTime datetime, @DateMarker datetime
	set @COUNTER = 0
	set @TOTALRECORDS = (select count(*) from #tmpSessions)

	while (@COUNTER < @TOTALRECORDS)
	begin
	
		set @UTIL_0000_0800 = 0
		set @UTIL_0800_1800 = 0
		set @UTIL_1800_0000 = 0
		
		-- get and store the info for the FROM record
		set @LoginTime = (select top(1) LoginTime from #tmpSessions)
		
		declare @fromHour int
		
		set @fromHour = datepart(hh, @LoginTime)
	
		if @fromHour >= 0 and @fromHour < 8 
			set @UTIL_0000_0800 = @UTIL_0000_0800 + 1
		else if @fromHour >= 8 and @fromHour < 18 
			set @UTIL_0800_1800 = @UTIL_0800_1800 + 1	
		else if @fromHour >= 18 and @fromHour < 24
			set @UTIL_1800_0000 = @UTIL_1800_0000 + 1
		
		delete top(1) from #tmpSessions
		
		update #counts set PERIOD1 = PERIOD1 + @UTIL_0000_0800, PERIOD2 = PERIOD2 + @UTIL_0800_1800, PERIOD3 = PERIOD3 + @UTIL_1800_0000 where DATE = (SELECT DATEADD(DAY, DATEDIFF(DAY, -1, @LoginTime), -1))
	
		set @COUNTER = @COUNTER + 1
	end	

	select * from #counts order by DATE desc
END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Sessions_GetPawsometerGeneralStats]    Script Date: 05/30/2012 16:33:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Sessions_GetPawsometerGeneralStats]

AS
BEGIN
	SET NOCOUNT ON

	declare @TotalSleeps1Hour bigint
	declare @TotalSleeps24Hour bigint
	declare @TotalLogins1Hour bigint
	declare @TotalLogins24Hour bigint
	
	set @TotalSleeps1Hour = (select count(*) as Total from PowerEventLogs where EventType = 'SLEEP_SERVER' and EventTime > DATEADD(hour,-1,GETDATE()))
	set @TotalSleeps24Hour = (select count(*) as Total from PowerEventLogs where EventType = 'SLEEP_SERVER' and EventTime > DATEADD(hour,-24,GETDATE()))

	set @TotalLogins1Hour = (select count(*) as Total from Sessions where LoginTime > DATEADD(hour,-1,GETDATE()))
	set @TotalLogins24Hour = (select count(*) as Total from Sessions where LoginTime > DATEADD(hour,-24,GETDATE()))
	
	select @TotalSleeps1Hour as Sleeps1Hour, @TotalSleeps24Hour as Sleeps24Hour, @TotalLogins1Hour as Logins1Hour, @TotalLogins24Hour as Logins24Hour
	
END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Sessions_GetTotalLoginCounts]    Script Date: 05/30/2012 16:33:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Sessions_GetTotalLoginCounts]
	@ToDate datetime,
	@Scale varchar(30),
	@Period smallint,
	@ComputerList varchar(MAX),
	@Delimiter varchar(2)
	
AS
BEGIN
	
	set nocount on
	
	CREATE TABLE #counts (
		
		YearVal smallint,
		MonthVal smallint,
		DayVal smallint,
		HourVal smallint,
		MinVal smallint,
		Count bigint
	)
	
	declare @COUNTER smallint
	declare @QUERY_START_DATE datetime
	
	set @COUNTER = 0
	set @QUERY_START_DATE = @ToDate
	
	select * into #computertable from paws_split_comma_separated_field(@ComputerList,@Delimiter)
	
	while (@COUNTER < @Period)

	begin
	
		declare @TOTAL_SESSIONS bigint
		
		if @Scale = 'MONTH'
		begin
			
			set @QUERY_START_DATE = DATEADD(day,-(DAY(DATEADD(month,1,@QUERY_START_DATE))-1),DATEADD(month,-1,@QUERY_START_DATE))
			
			if @ComputerList = '*'
				set @TOTAL_SESSIONS = (select count(*) from Sessions WHERE LoginTime > @QUERY_START_DATE and LoginTime < @ToDate)
			else
				set @TOTAL_SESSIONS = (select count(*) from Sessions WHERE LoginTime > @QUERY_START_DATE and LoginTime < @ToDate and exists (SELECT computer from paws_split_comma_separated_field(@ComputerList,@Delimiter) WHERE ComputerName = computer))
			
			set @ToDate = DATEADD(month,-1,@ToDate)
			
			insert into #counts (YearVal,MonthVal,DayVal,HourVal,MinVal,Count) values(
					DATEPART(year,@ToDate),
					DATEPART(month,@ToDate),
					null,
					null,
					null,
					@TOTAL_SESSIONS
			)
			
			
		end
		
		else if @Scale = 'DAY'
		begin
		
			set @QUERY_START_DATE = DATEADD(day,-1,@QUERY_START_DATE)
			
			if @ComputerList = '*'
				set @TOTAL_SESSIONS = (select count(*) from Sessions WHERE LoginTime > @QUERY_START_DATE and LoginTime < @ToDate)
			else
				set @TOTAL_SESSIONS = (select count(*) from Sessions WHERE LoginTime > @QUERY_START_DATE and LoginTime < @ToDate and exists (SELECT computer from paws_split_comma_separated_field(@ComputerList,@Delimiter) WHERE ComputerName = computer))
			
			insert into #counts (YearVal,MonthVal,DayVal,HourVal,MinVal,Count) values(
					DATEPART(year,@ToDate),
					DATEPART(month,@ToDate),
					DATEPART(day,@ToDate),
					null,
					null,
					@TOTAL_SESSIONS
			)
			
			set @ToDate = DATEADD(day,-1,@ToDate)
			
		end
		
		else if @Scale = 'HOUR'
		begin
		
			set @QUERY_START_DATE = DATEADD(hour,-1,@QUERY_START_DATE)
			
			if @ComputerList = '*'
				set @TOTAL_SESSIONS = (select count(*) from Sessions WHERE LoginTime > @QUERY_START_DATE and LoginTime < @ToDate)
			else
				set @TOTAL_SESSIONS = (select count(*) from Sessions WHERE LoginTime > @QUERY_START_DATE and LoginTime < @ToDate and exists (SELECT computer from paws_split_comma_separated_field(@ComputerList,@Delimiter) WHERE ComputerName = computer))
			
			insert into #counts (YearVal,MonthVal,DayVal,HourVal,MinVal,Count) values(
					DATEPART(year,@ToDate),
					DATEPART(month,@ToDate),
					DATEPART(day,@ToDate),
					DATEPART(hour,@ToDate),
					null,
					@TOTAL_SESSIONS
			)
			
			set @ToDate = DATEADD(hour,-1,@ToDate)
			
		end
		
		else if @Scale = 'MIN'
		begin
		
			set @QUERY_START_DATE = DATEADD(minute,-1,@QUERY_START_DATE)
			
			if @ComputerList = '*'
				set @TOTAL_SESSIONS = (select count(*) from Sessions WHERE LoginTime > @QUERY_START_DATE and LoginTime < @ToDate)
			else
				set @TOTAL_SESSIONS = (select count(*) from Sessions WHERE LoginTime > @QUERY_START_DATE and LoginTime < @ToDate and exists (SELECT computer from paws_split_comma_separated_field(@ComputerList,@Delimiter) WHERE ComputerName = computer))
			
			insert into #counts (YearVal,MonthVal,DayVal,HourVal,MinVal,Count) values(
					DATEPART(year,@ToDate),
					DATEPART(month,@ToDate),
					DATEPART(day,@ToDate),
					DATEPART(hour,@ToDate),
					DATEPART(minute,@ToDate),
					@TOTAL_SESSIONS
			)
			
			set @ToDate = DATEADD(minute,-1,@ToDate)
			
		end
		
		set @COUNTER = @COUNTER + 1
	end
	
	select * from #counts
END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[Sessions_GetUtilisation]    Script Date: 05/30/2012 16:33:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Sessions_GetUtilisation]
	@FromDate datetime,
	@ToDate datetime,
	@ComputerList varchar(MAX),
	@Delimiter varchar(2)
	
AS
BEGIN
	
	set nocount on
	
	CREATE TABLE #counts (
		
		DATE datetime,
		PERIOD1 bigint,
		PERIOD2 bigint,
		PERIOD3 bigint,
		TOTALVAL bigint
	)
	
	CREATE TABLE #tmpSessions (
		
		ComputerName varchar(255),
		UserName varchar(20),
		LoginTime datetime,
		LogoutTime datetime,
		Language varchar(50),
		ID bigint,
		BuildVersion varchar(20),
		Domain varchar(20),
		SerialNumber varchar(20),
		PAWSClientVersion varchar(50),
		OperatingSystem varchar(50)
	)
	
	select * into #computertable from paws_split_comma_separated_field(@ComputerList,@Delimiter)
	
	declare @initStartTime datetime, @initEndTime datetime
	set @initStartTime = @FromDate
	set @initEndTime = @ToDate
	declare @NumComputers bigint
	
	while @initStartTime < @initEndTime
	begin	
		insert into #counts (DATE,PERIOD1,PERIOD2,PERIOD3,TOTALVAL) values (@initStartTime,0,0,0,0)
		set @initStartTime = dateadd(day,1,@initStartTime)
	end
	
	declare @UTIL_0000_0800 bigint
	declare @UTIL_0800_1800 bigint
	declare @UTIL_1800_0000 bigint

	if @ComputerList = '*'
	begin
		insert into #tmpSessions select * from Sessions WHERE LoginTime > @FromDate and LoginTime < @ToDate and LogoutTime is not null
		set @NumComputers = (select count(distinct(ComputerName)) from Sessions)
	end
		
	else if charindex('%',@ComputerList) = 1
	begin
		set @ComputerList = (select replace(@ComputerList,'*',''))
		insert into #tmpSessions select * from Sessions WHERE LoginTime > @FromDate and LoginTime < @ToDate and LogoutTime is not null and ComputerName like @ComputerList
		set @NumComputers = (select count(distinct(ComputerName)) from Sessions where ComputerName like @ComputerList)
	end
	
	else
	begin
		insert into #tmpSessions select * from Sessions WHERE LoginTime > @FromDate and LoginTime < @ToDate and exists (SELECT computer from #computertable WHERE ComputerName = computer) and LogoutTime is not null	
		set @NumComputers = (select count(*) from #computertable)
	end
	
	declare @COUNTER bigint, @TOTALRECORDS bigint, @SESSION_LENGTH bigint, @FromTime datetime, @ToTime datetime, @DateMarker datetime
	set @COUNTER = 0
	set @TOTALRECORDS = (select count(*) from #tmpSessions)

	while (@COUNTER < @TOTALRECORDS)
	begin
		-- get and store the info for the FROM record
		set @FromTime = (select top(1) LoginTime from #tmpSessions)
		set @ToTime = (select top(1) LoginTime from #tmpSessions)

		delete top(1) from #tmpSessions
		
		select * into #itemCount from Generic_GetUtilisation(@FromTime,@ToTime)
		
		declare @COUNTER2 int
		set @COUNTER2 = 0
		declare @TOTALRECORDS2 int
		
		set @TOTALRECORDS2 = (select count(*) from #itemCount)
		
		while (@COUNTER2 < @TOTALRECORDS2)
		begin
			
			declare @tmpDate datetime, @tmpPeriod1 bigint, @tmpPeriod2 bigint, @tmpPeriod3 bigint
			
			set @tmpPeriod1 = (select top(1) period1 from #itemCount)
			set @tmpPeriod2 = (select top(1) period2 from #itemCount)
			set @tmpPeriod3 = (select top(1) period3 from #itemCount)
			set @tmpDate = (select top(1) date from #itemCount)
			
			declare @TotalTime bigint
			set @TotalTime = @NumComputers*24*60*60*1000
			
			update #counts set PERIOD1 = PERIOD1 + @tmpPeriod1, PERIOD2 = PERIOD2 + @tmpPeriod2,PERIOD3 = PERIOD3 + @tmpPeriod3, TOTALVAL = @TotalTime where DATE = @tmpDate
		
			-- pop the FROM record off the stack
			delete top(1) from #itemCount
		
			set @COUNTER2 = @COUNTER2 + 1
		end
		
		DROP table #itemCount
		
		set @COUNTER = @COUNTER + 1
	end	
	
	select * from #counts order by DATE desc
END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[SleepStats_Put]    Script Date: 05/30/2012 16:34:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SleepStats_Put]
	-- Add the parameters for the stored procedure here
	@OU varchar(750),
	@Date datetime,
	@SleepTime bigint,
	@KwhSavings decimal(18,2),
	@CO2Savings decimal(18,2),
	@CostSavings decimal(18,2)
AS
BEGIN
	SET NOCOUNT ON

	IF EXISTS (select OU,Date,SleepTime,KwhSavings,CO2Savings,CostSavings from SleepStats where Date = @Date)
		UPDATE SleepStats set SleepTime = @SleepTime, KwhSavings = @KwhSavings, CO2Savings = @CO2Savings, CostSavings = @CostSavings where OU =@OU and Date = @Date;
	ELSE
		INSERT INTO SleepStats (OU,Date,SleepTime,KwhSavings,CO2Savings,CostSavings) VALUES (@OU,@Date,@SleepTime,@KwhSavings,@CO2Savings,@CostSavings);

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[SleepStatsArchive_Get]    Script Date: 05/30/2012 16:34:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SleepStatsArchive_Get]
	-- Add the parameters for the stored procedure here
	@OU varchar(750),
	@Year int,
	@Month int,
	@Day int
AS
BEGIN
	SET NOCOUNT ON

	SELECT OU,Day,Month,Year,SleepTime,KwhSavings,CO2Savings,CostSavings from SleepStatsArchive where OU = @OU and Year = @Year and Month = @Month and Day = @Day

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[SleepStatsArchive_GetMonth]    Script Date: 05/30/2012 16:34:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SleepStatsArchive_GetMonth]
	-- Add the parameters for the stored procedure here
	@OU varchar(750),
	@Year int,
	@Month int
AS
BEGIN
	SET NOCOUNT ON

	SELECT OU,Day,Month,Year,SleepTime,KwhSavings,CO2Savings,CostSavings from SleepStatsArchive where OU like @OU and Year = @Year and Month = @Month

END

GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[SleepStatsArchive_GetYear]    Script Date: 05/30/2012 16:34:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SleepStatsArchive_GetYear]
	-- Add the parameters for the stored procedure here
	@OU varchar(750),
	@Year int
AS
BEGIN
	SET NOCOUNT ON

	SELECT OU,Day,Month,Year,SleepTime,KwhSavings,CO2Savings,CostSavings from SleepStatsArchive where OU like @OU and Year = @Year;

END


GO


USE [paws]
GO

/****** Object:  StoredProcedure [dbo].[SleepStatsArchive_Put]    Script Date: 05/30/2012 16:34:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SleepStatsArchive_Put]
	-- Add the parameters for the stored procedure here
	@OU varchar(750),
	@Year int,
	@Month int,
	@Day int,
	@SleepTime bigint,
	@KwhSavings decimal(18,2),
	@CO2Savings decimal(18,2),
	@CostSavings decimal(18,2)
AS
BEGIN
	SET NOCOUNT ON

	IF EXISTS (select OU,Day,Month,Year,SleepTime,KwhSavings,CO2Savings,CostSavings from SleepStatsArchive where OU =@OU and Year = @Year and Month = @Month and Day= @Day)
		UPDATE SleepStatsArchive set SleepTime = @SleepTime, KwhSavings = @KwhSavings, CO2Savings = @CO2Savings, CostSavings = @CostSavings where OU =@OU and Year = @Year and Month = @Month and Day= @Day;
	ELSE
		INSERT INTO SleepStatsArchive (OU,Year,Month,Day,SleepTime,KwhSavings,CO2Savings,CostSavings) VALUES (@OU,@Year,@Month,@Day,@SleepTime,@KwhSavings,@CO2Savings,@CostSavings);

END


GO


